#!/bin/bash
#设置任务异常退出
set -eo pipefail
dt='{{ execution_date | cst_ds }}'
name='广东代理区中心发件明细'
# 在退出时清理文件
trap "rm -f /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv && echo 'removed tmp files'" EXIT
# 创建txt文件
touch /tmp/${name}_${dt}.txt
#将中文字段名输入文件顶部
#将查询结果追加到文件中
{
  echo -e "代理区\t寄件网点\t运单号\t任务单号\t扫描时间\t实际重量\t体积重量\t长\t宽\t高\t物品类型名字\t">/tmp/${name}_${dt}.txt;
  spark-sql --driver-memory 4G --num-executors 20  --executor-memory 8g --executor-cores 6  --conf spark.executor.memoryOverhead=4g --conf spark.driver.maxResultSize=3g -S -e "
  with TAB_BARSCAN_ARRIVAL as (
    select waybill_no,    --运单号
           transfer_code, --任务号
           length,        --长
           width,         --宽
           height,        --高
           volume_weight, --体积重
           good_type,     --物品类型
           weight,        --高
           scan_site_code,
           scan_site,
           scan_time
    from (
             select waybill_no,                                                                           --运单号
                    transfer_code,                                                                        --任务号
                    length,                                                                               --长
                    width,                                                                                --宽
                    height,                                                                               --高
                    nvl(round(length * width * height / 8000, 4), 0)                    as volume_weight, --体积重
                    good_type,                                                                            --物品类型
                    weight,                                                                               --高
                    scan_time,
                    scan_site_code,
                    scan_site,
                    package_code,
                    row_number() over (partition by waybill_no order by scan_time asc ) as rn
             from jms_dwd.dwd_TAB_BARSCAN_ARRIVAL_base_dt
             where dt >= date_sub('{{ execution_date | cst_ds }}',30)
             and scan_type = '中心到件'
         ) tmp
    where tmp.rn = 1
    and to_date(scan_time) >= '{{ execution_date | cst_ds }}'
    and length < 50
    and width < 50
    and height < 50
    and (length + width + height) < 90
    and package_code is null
),
 tab_send_base as(
               select waybill_no,    --运单号
           scan_time
    from ( select waybill_no,
                    scan_time,
                    package_code,
                    row_number() over (partition by waybill_no order by scan_time asc ) as rn
             from jms_dwd.dwd_tab_barscan_centersend_base_dt
             where dt >= date_sub('{{ execution_date | cst_ds }}',30)
        and waybill_no not like 'B%'
         ) tmp
    where tmp.rn = 1
    and to_date(scan_time) = '{{ execution_date | cst_ds }}'
and package_code is null
 ),
     waybill_detail as (
         select pick_network_code,
                pick_network_name,
                waybill_no,
                goods_type_name,
                goods_name
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt >= date_sub('{{ execution_date | cst_ds }}',40)
     ),
     network_detal as (
         select code,
                agent_code,
                agent_name
         from jms_dim.dim_network_whole_massage
         where
              -- agent_name in('深圳代理区','粤东代理区')
           virt_name='广东代理区'    --虚拟代理区
     )
select
 agent_code,
 agent_name,
 pick_network_code,
 pick_network_name,
 waybill_no,
 transfer_code,
 scan_time,
 scan_site_code,
 scan_site,
 weight,
 volume_weight,
 length,
 width,
 height,
 goods_type_name
from (
    select
        c.agent_code
       ,c.agent_name
       ,b.pick_network_code
       ,b.pick_network_name
       ,a.waybill_no
       ,a.transfer_code
       ,a.scan_time
       ,a.scan_site_code
       ,a.scan_site
       ,a.weight
       ,a.volume_weight
       ,a.length
       ,a.width
       ,a.height
       ,b.goods_type_name
       ,case when b.goods_type_name in('易碎品','生鲜类','液体') and a.weight <2.0 and  a.weight >1.0 then 0
else 1 end as type_flag
from TAB_BARSCAN_ARRIVAL a
         join waybill_detail b
              on a.waybill_no = b.waybill_no
         join network_detal c
              on b.pick_network_code = c.code
              join tab_send_base d
              on a.waybill_no=d.waybill_no
         ) t
where t.type_flag=0;

" >>/tmp/${name}_${dt}.txt
}
# 替换分隔符，并输出csv
cat /tmp/${name}_${dt}.txt | tr -s '\t' ',' > /tmp/${name}_${dt}.csv
# 压缩并删除 csv 文件
zip -jrm /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv
# 上传文件
/usr/local/python3/bin/python3  /usr/local/airflow/dags/utils/operators/upload_oss_operation.py ${name}/${name}_${dt}.zip /tmp/${name}_${dt}.zip
# zip 文件将在进程退出后被自动清理

#声明文件大小
data_size=$(ls -lh /tmp/${name}_${dt}.zip | awk '{print $5}')
#打印文件信息
Message="${name}_${dt}.zip 上传成功 大小: $data_size"
#配置钉钉告警
curl 'https://oapi.dingtalk.com/robot/send?access_token=816a8f35fd84432ffa6bff9d7714abc322628bb4a8ded86908550d2335aba613' \
   -H 'Content-Type: application/json' \
   -d "
  {\"msgtype\": \"text\",
    \"text\": {
        \"content\": \"监控内容:$Message\"
     }
  }" || true
rm -f  /tmp/${name}_${dt}.txt